/* 
 *
 * Persistence4J - Simple library for data persistence using java
 * Copyright (c) 2010, Avdhesh yadav.
 * http://www.avdheshyadav.com
 * Contact: avdhesh.yadav@gmail.com
 *
 * This library is free software; you can redistribute it and/or modify it under the terms
 * of the GNU Lesser General Public License as published by the Free Software Foundation,
 * either version 3 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License along with this
 * library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
 * Boston, MA 02111-1307, USA.
 *
 */

package com.avdy.p4j.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import com.avdy.p4j.common.DAOException;
import com.avdy.p4j.jdbc.common.CRUD;
import com.avdy.p4j.jdbc.dao.util.DbUtils;
import com.avdy.p4j.jdbc.dbms.connfactory.DBConnector;

/**
 * @author Avdhesh Yadav    
 */
class MySqlDAOImpl
{
	public static String INSERT = "insert into ";

	public static String SELECT = "select * from ";

	public static String UPDATE = "update ";

	public static String DELETE = "delete from ";

	public static String WHERE = " where ";

	public static String AND = " and ";

	public static String OR = " or ";

	public static String EQUAL = " = ";
	
	public static String STAR=" * ";

	public static String ORDERBY = " order by ";

	public static String ASC = " asc";

	public static String DESC = " desc";
	
	public static String SET = " set ";
	
	public static final int initialPosition = 1;
	
	public static final int defaultCount = 10;
	
	private Connection m_Conn = null;

	private PreparedStatement pstmt = null;

	private ResultSet resultSet = null;

	static Logger logger = Logger.getLogger(MySqlDAOImpl.class.getName());

	private DBConnector m_Connector = null;
	
	private CRUD m_CRUD;
	
	
	/**
	 * 
	 * @param connector DBConnector
	 * @param crud CRUD
	 */
	public MySqlDAOImpl(DBConnector connector, CRUD crud)
	{
		m_Connector = connector;
		m_CRUD = crud;
	}
	

	/**
	 * 
	 */
	private String createQuery(String tableName, String queryType,String[] params, String[] values, String orderByField,String ascDesc)
	{
		String query = "";
		String st = "";
		if (queryType.equals(DELETE))
		{
			query = query.concat(DELETE) + tableName + WHERE;
		}
		if (queryType.equals(SELECT))
		{
			query = query.concat(SELECT) + tableName + WHERE;
		}
		int paramLength = params.length;
		logger.debug("paramLength:" + paramLength);
		for (int i = 0; i < paramLength; i++)
		{
			if (i > 0)
			{
				st = st + " and " + params[i] + "='" + values[i] + "'";
			} else
			{
				st = st + params[i] + "='" + values[i] + "'";
			}
		}
		if (orderByField != null)
		{
			orderByField = orderByField.concat(" ");
			st = st + ORDERBY + orderByField + ascDesc;
		}
		logger.debug("ST:" + st);
		query = query + st;
		logger.debug(query);
		return query;
	}

	
	/**
	 * 
	 */
	private ResultSet executeQuery(String tableName, String queryType,String[] params, String[] values, String orderByField,String ascDesc) throws DAOException
	{
		String query = createQuery(tableName, queryType, params, values,orderByField, ascDesc);
		logger.debug("query:"+query);
		m_Conn = m_Connector.getConnection();
		try
		{
			pstmt = m_Conn.prepareStatement(query,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			resultSet = m_CRUD.read(pstmt);
			return resultSet;
		}
		catch (SQLException exception)
		{
			exception.printStackTrace();
			closeConnection();
			throw new DAOException(exception.getMessage(), exception);
		}
	}

	
	/**
	 * 
	 */
	public void removeEntity(String tableName, String PkName,String PkValue) throws DAOException
	{
		String params[] = {PkName};
		String values[] = {PkValue};
		String query = createQuery(tableName, DELETE, params, values, null,null);
		m_Conn = m_Connector.getConnection();
		try
		{
			m_CRUD.delete(m_Conn, query);
		}
		catch (SQLException exception)
		{
			throw new DAOException(exception.getMessage(), exception);
		}
		finally
		{
			closeConnection();
		}
	}

	
	/**
	 * This method returns a list of VO object(example DomainsVO object)
	 * 
	 * @param tableName String
	 * @param param String
	 * @param value String
	 * @param orderByField  String
	 * @param ascDesc  String
	 * 
	 * @return List
	 * 
	 * @throws DAOException
	 */
	public ResultSet findByAttribute(String tableName,String param, String value, String orderByField, String ascDesc) throws DAOException
	{
		String[] params = {param};
		String[] values = {value};
		try
		{
			executeQuery(tableName, SELECT, params, values, orderByField,ascDesc);
			return resultSet;
		}
		catch (SQLException exception)
		{
			exception.printStackTrace();
			throw new DAOException(exception.getMessage(), exception);
		}
	}

	
	/**
	 * 
	 * @param tableName String
	 * @param params String[]
	 * @param values String[]
	 * @param orderByField String
	 * @param ascDesc String
	 * 
	 * @return ResultSet
	 * 
	 * @throws DAOException
	 */
	public ResultSet find(String tableName,String[] params, String[] values, String orderByField,String ascDesc) throws DAOException
	{
		try
		{
			resultSet  = executeQuery(tableName, SELECT, params, values, orderByField,ascDesc);
			return resultSet;
		}
		catch (SQLException exception)
		{
			throw new DAOException(exception.getMessage(), exception);
		}
	}

	
	/**
	 * 
	 * @param query String
	 * 
	 * @return ResultSet
	 * 
	 * @throws DAOException
	 */
	public ResultSet findByQuery(String query) throws DAOException
	{
		m_Conn = m_Connector.getConnection();
		try
		{
			pstmt = m_Conn.prepareStatement(query,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			resultSet = m_CRUD.read(pstmt);
			return resultSet;
		}
		catch (SQLException exception)
		{
			throw new DAOException(exception.getMessage(), exception);
		}
	}
	
	
	/**
	 * 
	 * @param tableName String
	 * @param params String
	 * @param value String
	 * 
	 * @return int
	 * 
	 * @throws DAOException
	 */
	public int countRows(String tableName, String params, String value)throws DAOException
	{
		String query = "select count(*) from " + tableName;
		if (params != null && value != null && !(params.equals("")))
		{
			query = query + WHERE + params + EQUAL + "'" + value + "'";
		}
		int rows = 0;
		m_Conn = m_Connector.getConnection();
		try
		{
			pstmt = m_Conn.prepareStatement(query);
			resultSet = m_CRUD.read(pstmt);
			if (resultSet.next())
			{
				return resultSet.getInt(1);
			}
		}
		catch (SQLException exception)
		{
			throw new DAOException(exception.getMessage(), exception);
		}
		finally
		{
			closeConnection();
		}
		return rows;
	}
	

	/**
	 * this methods closes the connection
	 */
	public void closeConnection()
	{
		logger.debug("close Connection:" + m_Conn);
		DbUtils.closeQuietly(m_Conn, pstmt, resultSet);
	}
}